<?php

$db = new PGSQLConnector('u_superuser');

$personSQL = "SELECT p.idperson,p.firstname, p.lastname, p.email, p.birthdate, p.sex, p.maritalstatus,
       pa1.line1 AS termline1,
       pa1.line2 AS termline2,
       pa1.postcode AS termpostcode,
       pc1.city AS termcity,
       pa1.countrycode AS termcountrycode,
       co1.postalfix AS termpostalfix,
       co1.phonecode AS termphonecode,
       pa2.line1 AS homeline1,
       pa2.line2 AS homeline2,
       pa2.postcode AS homepostcode,
       pc2.city AS homecity,
       pa2.countrycode AS homecountrycode,
       co2.postalfix AS homepostalfix,
       co2.phonecode AS homephonecode,
       pp1.domesticnumber AS mobilephone,
       pp2.domesticnumber AS privatephone,
       ps.passportnumber, ps.issuedby, ps.issuedate, ps.expires,
       s.nationality, s.medicallyfit, s.medicalstatus, s.idfaculty,
       s.pointsrequired, s.pointsbynow, s.yearsrequired, s.specialization,
       s.appliedbefore, s.previousyear,
       sf.studyfield
FROM person p
LEFT JOIN personaddress pa1 ON pa1.idperson = p.idperson AND pa1.addresstypeperson = 'Study'
LEFT JOIN personaddress pa2 ON pa2.idperson = p.idperson AND pa2.addresstypeperson = 'Home'
LEFT JOIN postcode pc1 ON pc1.postcode = pa1.postcode AND pa1.countrycode = pc1.countrycode
LEFT JOIN postcode pc2 ON pc2.postcode = pa2.postcode AND pa2.countrycode = pc2.countrycode
LEFT JOIN personphone pp1 ON pp1.idperson = p.idperson AND pp1.phonetypeperson = 'Private mobile'
LEFT JOIN personphone pp2 ON pp2.idperson = p.idperson AND pp2.phonetypeperson = 'Private'
LEFT JOIN country co1 ON co1.countrycode = pc1.countrycode
LEFT JOIN country co2 ON co2.countrycode = pc2.countrycode
LEFT JOIN student s ON s.idstudent = p.idperson
LEFT JOIN passport ps ON ps.idstudent = p.idperson
LEFT JOIN (
    SELECT idstudent, studyfield
    FROM student_studyfield
    WHERE idstudent = '" . $l->getPersonid() . "'
    LIMIT 1
) sf ON sf.idstudent = s.idstudent
WHERE p.idperson = '" . $l->getPersonid() . "'";
$person = $db->query2array($personSQL);

//Setting up the session
$tmp = Array();
$tmpstudy = Array();

foreach($person as $p) {
    isset($p['firstname']) ? $tmp['firstname'] = $p['firstname'] : $tmp['firstname'] = null;
    isset($p['lastname']) ? $tmp['lastname'] = $p['lastname'] : $tmp['lastname'] = null;
    isset($p['birthdate']) ? list($tmp['bornYear'],$tmp['bornMonth'],$tmp['bornDay']) = explode("-", $p['birthdate']) : $tmp['bornDay'] = $tmp['bornMonth'] = $tmp['bornYear'] = null;
    isset($p['sex']) ? $tmp['sex'] = $p['sex'] : $tmp['sex'] = null;
    isset($p['maritalstatus']) ? $tmp['maritalstatus'] = $p['maritalstatus'] : $tmp['maritalstatus'] = null;
    isset($p['medicallyfit']) ? $tmp['medicallyfit'] = $p['medicallyfit'] : $tmp['medicallyfit'] = null;
    isset($p['medicalstatus']) ? $tmp['medicalstatus'] = $p['medicalstatus'] : $tmp['medicalstatus'] = null;
    isset($p['termline1']) ? $tmp['termAddr1'] = $p['termline1'] : $tmp['termAddr1'] = null;
    isset($p['termline2']) ? $tmp['termAddr2'] = $p['termline2'] : $tmp['termAddr2'] = null;
    isset($p['termpostcode']) ? $tmp['termPostalCode'] = $p['termpostcode'] : $tmp['termPostalCode'] = null;
    isset($p['homeline1']) ? $tmp['homeAddr1'] = $p['homeline1'] : $tmp['homeAddr1'] = null;
    isset($p['homeline2']) ? $tmp['homeAddr2'] = $p['homeline2'] : $tmp['homeAddr2'] = null;
    isset($p['homepostcode']) ? $tmp['homePostalCode'] = $p['homepostcode'] : $tmp['homePostalCode'] = null;
    isset($p['privatephone']) ? $tmp['privatePhone'] = $p['privatephone'] : $tmp['privatePhone'] = null;
    isset($p['nationality']) ? $tmp['nationality'] = $p['nationality'] : $tmp['nationality'] = null;
    isset($p['mobilephone']) ? $tmp['mobilephone'] = $p['mobilephone'] : $tmp['mobilephone'] = null;
    //Tmp Study
    isset($p['pointsrequired']) ? $tmpstudy['pointsreq'] = $p['pointsrequired'] : $tmpstudy['pointsreq'] = null;
    isset($p['pointsbynow']) ? $tmpstudy['pointsattained'] = $p['pointsbynow'] : $tmpstudy['pointsattained'] = null;
    isset($p['yearsrequired']) ? $tmpstudy['yearsreq'] = $p['yearsrequired'] : $tmpstudy['yearsreq'] = null;
    isset($p['specialization']) ? $tmpstudy['specialization'] = $p['specialization'] : $tmpstudy['specialization'] = null;
    isset($p['studyfield']) ? $tmpstudy['fieldofstudy'] = $p['studyfield'] : $tmpstudy['fieldofstudy'] = null;
    isset($p['idfaculty']) ? $tmpstudy['university'] = $p['idfaculty'] : $tmpstudy['university'] = null;
}
isset($_SESSION['personal']) ? $_SESSION['personal'] : $_SESSION['personal'] = $tmp;
isset($_SESSION['study']) ? $_SESSION['study'] : $_SESSION['study'] = $tmpstudy;
unset($tmpstudy);
unset($tmp);
?>
